/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package myetl;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.ListIterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Date;

/**
 *
 * @author apuangr1
 */
public class DestData {

    String table;
    String sql, insertSql;
    String columns;
    AppManager apm;
    ResultSet result;
    ArrayList<Column> colsInfo;
    int numberOfColumn;
    DatabaseMetaData meta;
    ArrayList<Column> keys;

    DestData(AppManager apm) {
        keys = new ArrayList<Column>();
        colsInfo = new ArrayList<Column>();
        this.apm = apm;
        this.apm.setDestData(this);
        this.table = this.apm.getDestTable();
        this.columns = this.apm.getDestColumns();
        String temp[] = columns.split(",");
        Column dummycol = new Column();
        colsInfo.add(dummycol);
        for (int i = 1; i <= temp.length; i++) {
            Column col = new Column();
            col.setNumber(i);
            col.setName(temp[i - 1].trim());
            colsInfo.add(col);
        }


        retrieve();
//        buildInsertStatement();
        getKeyColums();

//        getUpdateCriteria();

        //        retrieve();
        //        retrieve();

    }

//    private void buildInsertStatement() {
//        System.out.println("---------- buildInsertStatement ----------");
//        Iterator<Column> it = this.colsInfo.iterator();
//        insertSql = "insert into " + table + "  (" + columns + " ) values ( ";
//        while (it.hasNext()) {
//            it.next();
//            insertSql = insertSql + "?,";
//        }
//        insertSql = insertSql.substring(0, insertSql.length() - 1) + ")";
//
////        System.out.println(insertSql);
//    }
    public void retrieve() {
        System.out.println("---------- retrieve ----------");
        try {
            Statement stmt = this.apm.getDestConn().createStatement();
            sql = "select " + columns + "  from " + table;
            result = stmt.executeQuery(sql);
            result.setFetchSize(1);
            ResultSetMetaData md = result.getMetaData();

            numberOfColumn = md.getColumnCount();

            for (int i = 1; i <= numberOfColumn; i++) {
//                System.out.print(md.getColumnTypeName(i).toUpperCase());
                this.colsInfo.get(i).setType(md.getColumnTypeName(i).toUpperCase());
            }

            //System.out.println("==");
        } catch (SQLException ex) {
            Logger.getLogger(DestData.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public String getColumnType(int col) {
        return this.colsInfo.get(col).getType();
    }

    public String getKeyType(int col) {
        return this.keys.get(col).getType();
    }

    public String getColumnType(String col) {
        int size = colsInfo.size();
        for (int i = 1; i <= size; i++) {
            if (this.colsInfo.get(i).getName().toUpperCase().equals(col.toUpperCase())) {
                return this.colsInfo.get(i).getType();
            }

        }
        return "Error";
    }

    public String getColumnName(int col) {
        return this.colsInfo.get(col).getName();
    }

    public void moveNext() {
        try {
            result.next();
        } catch (SQLException ex) {
            Logger.getLogger(DestData.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void setValue(int column, String value) {


        this.colsInfo.get(column).setStringvalue(value);


        //System.out.println("string:" + value);


    }

    public void setValue(int column, int value) {
        this.colsInfo.get(column).setInvalue(value);


        //System.out.println("int:" + value);

    }

    public void setValue(int column, double value) {
        this.colsInfo.get(column).setDecimalvalue(value);

//        System.out.println("double:" + value);

    }

    public void setValue(int column, Date value) {
        this.colsInfo.get(column).setDatevalue(value);

//        System.out.println("double:" + value);

    }

    public void setFunction(int column, String function) {
        this.colsInfo.get(column).setFunction(function);

//        System.out.println("double:" + value);

    }
//    public String getString

    public String getString(int column) {

        if (!this.colsInfo.get(column).getType().contains("CHAR")) {
//            System.out.println("error: getstring" + cols[column - 1]);
//            return "Error";
        }
        return this.colsInfo.get(column).getStringvalue();

    }

    public String getString(String column) {
        int size = colsInfo.size();
        for (int i = 1; i <= size; i++) {
            if (this.colsInfo.get(i).getName().toUpperCase().equals(column.toUpperCase())) {
                return this.colsInfo.get(i).getStringvalue();
            }


        }
        return "Error";
    }

    public double getDecimal(String column) {
        int size = colsInfo.size();
        for (int i = 1; i <= size; i++) {
            if (this.colsInfo.get(i).getName().toUpperCase().equals(column.toUpperCase())) {
                return this.colsInfo.get(i).getDecimalvalue();
            }


        }
        return -9999.99999;
    }

    public int getInteger(String column) {
        int size = colsInfo.size();
        for (int i = 1; i <= size; i++) {
            if (this.colsInfo.get(i).getName().toUpperCase().equals(column.toUpperCase())) {
                return this.colsInfo.get(i).getInvalue();
            }


        }
        return -9999;
    }

    public java.sql.Date getDate(String column) {
        int size = colsInfo.size();
        for (int i = 1; i <= size; i++) {
            if (this.colsInfo.get(i).getName().toUpperCase().equals(column.toUpperCase())) {
                return this.colsInfo.get(i).getDatevalue();
            }


        }
        return null;
    }

    public double getDecimal(int column) {

        if (!Utility.isDecimalColumn(this.colsInfo.get(column).getType())) {
//            System.out.println("error: getDecimal");
            return -9999.99999;
        }
        return this.colsInfo.get(column).getDecimalvalue();

    }

    public int getInteger(int column) {

        if (!this.colsInfo.get(column).getType().contains("INT")) {
            //          System.out.println("error: Integer");
            return -9999;
        }
        return this.colsInfo.get(column).getInvalue();

    }

    public java.sql.Date getDate(int column) {

        if (!this.colsInfo.get(column).getType().contains("DATE")) {
            //          System.out.println("error: Integer");
            return null;
        }
        return this.colsInfo.get(column).getDatevalue();

    }

    public String getFunction(int column) {

        return this.colsInfo.get(column).getFunction();

    }

    public void addToDB() {
    }

    public void update() {
    }

    public void clone(SourceData sourcedata) {
    }

    public void getKeyColumsFromDB() {
        System.out.println("---------- getKeyColums ----------");
        try {
            ResultSet keySet = null;

            meta = apm.getDestConn().getMetaData();
            //keySet = meta.getPrimaryKeys(null, null, table);
            keySet = meta.getBestRowIdentifier(apm.getDestConn().getCatalog(), null, table, DatabaseMetaData.bestRowTemporary, false);
            keySet.setFetchSize(10);
            int i = 1;
            keys.add(new Column());
            while (keySet.next()) {
                Column key = new Column();
                key.setNumber(i);
                key.setType(this.colsInfo.get(i).getType());
                key.setName(keySet.getString("COLUMN_NAME"));
                keys.add(key);
                //System.out.println("PK: " + keySet.getString("COLUMN_NAME"));

                i++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DestData.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public void getKeyColums() {
        System.out.println("---------- getKeyColums ----------");


        String[] keyColumns = apm.getDestKeys().split(",");
        //keySet = meta.getPrimaryKeys(null, null, table);
        int i = 1;
        keys.add(new Column());
        for (i = 1; i <= keyColumns.length; i++) {
            //while (keySet.next()) {
            Column key = new Column();
            key.setNumber(i);
            key.setType(this.getColumnType(keyColumns[i - 1].replace(" ", "")));
            key.setName(keyColumns[i - 1].trim().replace(" ", ""));
            keys.add(key);
            System.out.println("PK: " + keyColumns[i - 1]);


        }


    }

    public String getUpdateCriteria() {
        System.out.println("---------- getUpdateCriteria ----------");
        String where = "";

        Iterator<Column> li = keys.listIterator();
        li.next();
        while (li.hasNext()) {
            where = where + li.next().getName() + " =? and ";

        }
        System.out.println("++++++++++++++++++++" + where);
        return where.substring(0, where.length() - 4);
    }

    public void listData() {
        System.out.println("---------- listData ----------");
        Iterator<Column> li = this.colsInfo.iterator();
        Column col;
        li.next();
        while (li.hasNext()) {
            col = li.next();
//            System.out.print(col.getName() + ":" + col.getType() + ":");
            if (col.getType().contains("CHAR")) {
                System.out.println(col.getStringvalue());
            }
            if (Utility.isDecimalColumn(col.getType())) {
                System.out.println(col.getDecimalvalue());
            }
            if (col.getType().contains("INT")) {
                System.out.println(col.getInvalue());
            }
            if (col.getType().contains("DATE")) {
                System.out.println(col.getDatevalue().toString());
            }
        }


    }

    public void listColumn() {
        System.out.println("---------- Lisat column ----------");
        Iterator<Column> li = this.colsInfo.iterator();
        Column col;
        li.next();
        while (li.hasNext()) {
            col = li.next();

            System.out.println(col.getType());

        }
        System.out.println("---------- end Lisat column ----------");

    }

    public void listName() {
        System.out.println("---------- List name ----------");

        Iterator<Column> li = this.colsInfo.iterator();
        Column col;
        while (li.hasNext()) {
            col = li.next();

            System.out.println(col.getName());

        }
        System.out.println("---------- end List column ----------");

    }

    public void setKeyValue() {
        for (int i = 1; i < keys.size(); i++) {
            if (keys.get(i).getType().contains("CHAR")) {
                keys.get(i).setStringvalue(this.getString(keys.get(i).getName()));
            }
            if (Utility.isDecimalColumn(keys.get(i).getType())) {
                keys.get(i).setDecimalvalue(this.getDecimal(keys.get(i).getName()));
            }
            if (keys.get(i).getType().contains("INT")) {
                keys.get(i).setInvalue(this.getInteger(keys.get(i).getName()));
            }
            if (keys.get(i).getType().contains("DATE")) {
                keys.get(i).setDatevalue(this.getDate(keys.get(i).getName()));
            }
        }
    }

    public String getStringKey(int column) {

        if (!this.keys.get(column).getType().contains("CHAR")) {
//            System.out.println("error: getstring" + cols[column - 1]);
//            return "Error";
        }
        return this.keys.get(column).getStringvalue();

    }

    public int getIntegerKey(int column) {

        if (!this.keys.get(column).getType().contains("CHAR")) {
//            System.out.println("error: getstring" + cols[column - 1]);
//            return "Error";
        }
        return this.keys.get(column).getInvalue();

    }

    public double getDecimalKey(int column) {

        if (!this.keys.get(column).getType().contains("CHAR")) {
//            System.out.println("error: getstring" + cols[column - 1]);
//            return "Error";
        }
        return this.keys.get(column).getDecimalvalue();

    }

    public int getNumberOfKey() {
        return keys.size() - 1;
    }

    String getKeyName(int col) {
        return this.keys.get(col).getName();
    }

    public java.sql.Date getDateKey(int column) {
        if (!this.keys.get(column).getType().contains("DATE")) {
//            System.out.println("error: getstring" + cols[column - 1]);
//            return "Error";
        }
        return this.keys.get(column).getDatevalue();
    }
}
